package org.kl.bf.repository.basic;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.joda.time.DateTime;
import org.kl.bf.entity.basic.ExcelConfig;
import org.kl.bf.entity.basic.ExcelImport;
import org.kl.bf.service.ServiceException;
import org.kl.bf.utils.BasicConstants;
import org.kl.bf.utils.KLConvertUtils;
import org.kl.bf.utils.UUIDGenerator;
import org.kl.bf.web.base.BasicController;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

/**
 * @Author hqm
 * @CreateTime 2014年4月24日 下午9:01:48
 */
@Component
public class ExcelJDBCDao {
	private JdbcTemplate jdbcTemp;

	@Resource
	public void setDataSource(DataSource dataSource) {
		jdbcTemp = new JdbcTemplate(dataSource);
	}

	/**
	 * 将Excel数据插入或者更新数据库
	 * 
	 * @param excelImport
	 * @param configMap
	 * @param excelData
	 * @return 数组，包含两个值{插入条数，更新条数}
	 */
	public int[] importDBFromExcel(ExcelImport excelImport, final Map<Integer, ExcelConfig> configMap, int notIssistColumnCount,
			List<String[]> excelData) {
		int columnCount = notIssistColumnCount < excelData.get(0).length ? notIssistColumnCount : excelData.get(0).length;
		List<Integer> updateColNos = new ArrayList<Integer>();// 更新字段所在的列号，从1开始
		List<Integer> whereColNos = new ArrayList<Integer>();// where字段所在的列号，从1开始
		if (excelImport.getIsOverride() != BasicConstants.YES_INT) {// 不覆盖数据则全部为插入数据
			for (int i = 0; i < excelData.size(); i++) {
				for (Integer j = 0; j < columnCount; j++) {
					if (configMap.get(j + 1).getIsAssistColumn() != BasicConstants.YES_INT) {
						if (configMap.get(j + 1).getIsForeignKey() == BasicConstants.YES_INT) {// 如果为外键则获取真实关联表ID
							excelData.get(i)[j] = getDataFromDB(excelData.get(i)[j], excelData.get(i), configMap.get(j + 1), j + 1, i + 1);
						} else {
							if (configMap.get(j + 1).getColumnConvertMap() != null) {
								excelData.get(i)[j] = getDataFromMap(excelData.get(i)[j], configMap.get(j + 1), j + 1, i + 1);
							}
						}
					}
				}
			}
			int[] iResult = insertData(excelImport, configMap, columnCount, excelData);
			return new int[] { iResult.length, 0 };
		} else {
			List<String[]> updateExcelData = new ArrayList<String[]>();
			List<String[]> insertExcelData = new ArrayList<String[]>();
			validData(excelImport, configMap, excelData, insertExcelData, updateExcelData, columnCount, updateColNos, whereColNos);
			int[] iResult = insertData(excelImport, configMap, columnCount, insertExcelData);
			if (updateExcelData.isEmpty()) {
				return new int[] { iResult.length, 0 };
			} else {
				int[] uResult = updateData(excelImport, configMap, updateExcelData, updateColNos, whereColNos);
				int totalUpdateCount = 0;
				for (int i : uResult) {
					totalUpdateCount += i;
				}
				totalUpdateCount = totalUpdateCount / 2;
				return new int[] { iResult.length, Math.abs(totalUpdateCount) };
			}
		}
	}

	/**
	 * 插入新数据
	 * 
	 * @param excelImport
	 * @param configMap
	 * @param columnCount
	 * @param insertExcelData
	 * @return
	 */
	private int[] insertData(final ExcelImport excelImport, final Map<Integer, ExcelConfig> configMap, final int columnCount,
			final List<String[]> insertExcelData) {
		String sql = createInsertSQL(excelImport, configMap, columnCount);
		final String[] attachmentIds = new String[insertExcelData.size()];
		BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				for (Integer j = 0; j < columnCount; j++) {
					try {
						ps.setObject(j + 1, KLConvertUtils.convertStringToObject(
								StringUtils.isNotBlank(insertExcelData.get(i)[j]) ? insertExcelData.get(i)[j] : null, configMap.get(j + 1)
										.getPropertyClass()));
					} catch (Exception e) {
						e.printStackTrace();
						throw new SQLException("第"+i+"行第"+j+"列:"+Arrays.toString(insertExcelData.get(i))+","+e.getMessage());
					}
				}
				ps.setObject(columnCount + 1, UUIDGenerator.getUUID());
				ps.setObject(columnCount + 2, BasicController.getCurrentUserId());
				ps.setObject(columnCount + 3, BasicController.getCurrentUserName());
				ps.setObject(columnCount + 4, new Timestamp(new DateTime().getMillis()));
				ps.setObject(columnCount + 5, BasicController.getCurrentUserDeptId());
				if (excelImport.getWithAttachment() == BasicConstants.YES_INT) {
					attachmentIds[i] = UUIDGenerator.getUUID();
					ps.setObject(columnCount + 6, attachmentIds[i]);
				}
			}

			public int getBatchSize() {
				return insertExcelData.size();
			}
		};
		int[] result = jdbcTemp.batchUpdate(sql, setter);
		if (excelImport.getWithAttachment() == BasicConstants.YES_INT) {
			String attachmentSql = "insert into basic_attachment_master(id) values(?)";
			BatchPreparedStatementSetter attachmentSetter = new BatchPreparedStatementSetter() {
				public void setValues(PreparedStatement ps, int i) throws SQLException {
					ps.setObject(1, attachmentIds[i]);
				}

				public int getBatchSize() {
					return attachmentIds.length;
				}
			};
			jdbcTemp.batchUpdate(attachmentSql, attachmentSetter);
		}
		return result;
	}

	/**
	 * 更新已有数据
	 * 
	 * @param excelImport
	 * @param configMap
	 * @param updateExcelData
	 * @param updateColNos
	 * @param whereColNos
	 * @return
	 */
	private int[] updateData(ExcelImport excelImport, final Map<Integer, ExcelConfig> configMap, final List<String[]> updateExcelData,
			final List<Integer> updateColNos, final List<Integer> whereColNos) {
		String sql = createUpdateSQL(excelImport.getTableName(), configMap, updateColNos, whereColNos);
		BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				int uSize = updateColNos.size();
				int wSize = whereColNos.size();
				for (Integer j = 0; j < uSize; j++) {// update的字段值
					try {
						ps.setObject(
								j + 1,
								KLConvertUtils.convertStringToObject(updateExcelData.get(i)[updateColNos.get(j) - 1],
										configMap.get(updateColNos.get(j)).getPropertyClass()));
					} catch (Exception e) {
						e.printStackTrace();
						throw new SQLException("第"+i+"行第"+j+"列:"+Arrays.toString(updateExcelData.get(i))+","+e.getMessage());
					}
				}
				ps.setObject(uSize + 1, BasicController.getCurrentUserId());// 最后修改人ID
				ps.setObject(uSize + 2, BasicController.getCurrentUserName());// 最后修改人
				ps.setObject(uSize + 3, new Timestamp(new DateTime().getMillis()));
				for (Integer j = 0; j < wSize; j++) {// where的字段值
					try {
						ps.setObject(
								j + 4 + uSize,
								KLConvertUtils.convertStringToObject(updateExcelData.get(i)[whereColNos.get(j) - 1],
										configMap.get(whereColNos.get(j)).getPropertyClass()));
					} catch (Exception e) {
						e.printStackTrace();
						throw new SQLException("第"+i+"行第"+j+"列:"+Arrays.toString(updateExcelData.get(i))+","+e.getMessage());
					}
				}
			}

			public int getBatchSize() {
				return updateExcelData.size();
			}
		};
		int[] result = jdbcTemp.batchUpdate(sql, setter);
		return result;
	}

	/**
	 * 检测数据正确性，获取外键值 。将数据分为插入和修改两部分数据
	 * 
	 * @param excelImport
	 * @param configMap
	 * @param originExcelData
	 *            原始Excel数据
	 * @param insertExcelData
	 *            插入的数据
	 * @param updateExcelData
	 *            修改的数据
	 * @param columnCount
	 *            可导入的列数
	 * @param updateColNos
	 *            更新字段所在的列编号集
	 * @param whereColNos
	 *            作为查询条件的列编号集
	 */
	private void validData(ExcelImport excelImport, Map<Integer, ExcelConfig> configMap, List<String[]> originExcelData,
			List<String[]> insertExcelData, List<String[]> updateExcelData, int columnCount, List<Integer> updateColNos,
			List<Integer> whereColNos) {
		if (excelImport != null && configMap != null && originExcelData != null && insertExcelData != null && updateExcelData != null) {

			analysisCol(configMap, columnCount, updateColNos, whereColNos);
			if (excelImport.getIsOverride() == BasicConstants.YES_INT && whereColNos.isEmpty()) {// 如果是覆盖数据但是没有设置联合主键则抛出异常
				throw new ServiceException(excelImport.getImportNo() + "是覆盖数据，但是没有设置联合主键");
			}
			for (int i = 0; i < originExcelData.size(); i++) {
				for (Integer j = 0; j < columnCount; j++) {
					if (configMap.get(j + 1).getIsAssistColumn() != BasicConstants.YES_INT) {
						if (configMap.get(j + 1).getIsForeignKey() == BasicConstants.YES_INT) {// 如果为外键则获取真实关联表ID
							originExcelData.get(i)[j] = getDataFromDB(originExcelData.get(i)[j], originExcelData.get(i),
									configMap.get(j + 1), j + 1, i + 1);
						} else {
							if (configMap.get(j + 1).getColumnConvertMap() != null) {
								originExcelData.get(i)[j] = getDataFromMap(originExcelData.get(i)[j], configMap.get(j + 1), j + 1, i + 1);
							}
						}
					}
				}
			}
			String countSql = getSelectCountSql(excelImport, configMap, whereColNos);
			for (int i = 0; i < originExcelData.size(); i++) {
				if (isExist(originExcelData.get(i), configMap, whereColNos, countSql)) {
					updateExcelData.add(originExcelData.get(i));
				} else {
					insertExcelData.add(originExcelData.get(i));
				}
			}
		}
	}

	/**
	 * 判断行数据是否在数据库
	 * 
	 * @param data
	 * @param configMap
	 * @param whereColNos
	 * @param countSql
	 * @return
	 */
	private boolean isExist(String[] data, Map<Integer, ExcelConfig> configMap, List<Integer> whereColNos, String countSql) {
		String[] whereValue = new String[whereColNos.size()];
		for (int i = 0; i < whereColNos.size(); i++) {
			whereValue[i] = data[whereColNos.get(i) - 1];// 由于whereColNos中的是配置中的列编号，从1开始，所以减1
		}
		List<Integer> result = jdbcTemp.queryForList(countSql, whereValue, Integer.class);
		if (result == null || result.isEmpty() || result.get(0) == 0) {
			return false;
		} else {
			return true;
		}
	}

	/**
	 * 生成查询条数语句
	 * 
	 * @param excelImport
	 * @param configMap
	 * @param whereColNos
	 * @return
	 */
	private String getSelectCountSql(ExcelImport excelImport, Map<Integer, ExcelConfig> configMap, List<Integer> whereColNos) {
		StringBuilder sql = new StringBuilder("select count(1) from ");
		sql.append(excelImport.getTableName() + " where 1=1 ");
		for (Integer i : whereColNos) {
			sql.append(" and " + configMap.get(i).getColumnName() + "=? ");
		}
		return sql.toString();
	}

	/**
	 * Excel列分析，如果是更新数据，区分Where列和Update列
	 * 
	 * @param configMap
	 * @param columnCount
	 * @param updateColNos
	 * @param whereColNos
	 */
	private void analysisCol(Map<Integer, ExcelConfig> configMap, int columnCount, List<Integer> updateColNos, List<Integer> whereColNos) {
		int maxColNoForWhere = 0;// where条件列所在的最大列号
		for (Integer key : configMap.keySet()) {
			if (configMap.get(key).getIsUnionUnique() == BasicConstants.YES_INT) {// 是联合主键字段
				maxColNoForWhere = maxColNoForWhere > key ? maxColNoForWhere : key;
			}
		}
		for (int i = 1; i <= columnCount; i++) {
			if (configMap.get(i).getIsAssistColumn() != BasicConstants.YES_INT) {
				if (configMap.get(i).getIsUnionUnique() == BasicConstants.YES_INT) {// 是联合主键字段
					whereColNos.add(i);
					maxColNoForWhere = maxColNoForWhere > i ? maxColNoForWhere : i;
				} else {
					updateColNos.add(i);
				}
			}
		}
		// 如果where条件列不在Excel列中则抛出异常
		if (columnCount < maxColNoForWhere) {
			throw new ServiceException("联合主键列[" + configMap.get(maxColNoForWhere).getColumnName() + "]不在Excel中，其应该位于第" + maxColNoForWhere
					+ "列");
		}
	}

	/**
	 * 生成insert sql
	 * 
	 * @param tableName
	 * @param configMap
	 * @param columnCount
	 * @return
	 */
	private String createInsertSQL(ExcelImport excelImport, Map<Integer, ExcelConfig> configMap, Integer columnCount) {
		StringBuilder sql = new StringBuilder();
		StringBuilder valuesSql = new StringBuilder();
		sql.append("insert into " + excelImport.getTableName() + " (");
		for (Integer i = 1; i <= columnCount; i++) {
			sql.append(configMap.get(i).getColumnName() + ",");
			valuesSql.append("?,");
		}
		sql.append("id,create_by,create_man,create_time,create_dept");
		if (excelImport.getWithAttachment() == BasicConstants.YES_INT) {
			sql.append(",attachment_id");
		}
		valuesSql.append("?,?,?,?,?");
		if (excelImport.getWithAttachment() == BasicConstants.YES_INT) {
			valuesSql.append(",?");
		}
		sql.append(") values(");
		sql.append(valuesSql + ")");
		return sql.toString();
	}

	/**
	 * 生成update sql
	 * 
	 * @param tableName
	 * @param configMap
	 * @param updateColNos
	 * @param whereColNos
	 * @return
	 */
	private String createUpdateSQL(String tableName, Map<Integer, ExcelConfig> configMap, List<Integer> updateColNos,
			List<Integer> whereColNos) {
		StringBuilder sql = new StringBuilder("update " + tableName + " set ");
		for (Integer i : updateColNos) {
			sql.append(configMap.get(i).getColumnName() + "=?, ");
		}
		sql = sql.append(" create_by=?, ");
		sql = sql.append(" create_man=?, ");
		sql = sql.append(" create_time=?");
		sql.append(" where 1=1 ");
		for (Integer i : whereColNos) {
			sql.append(" and " + configMap.get(i).getColumnName() + "=? ");
		}

		return sql.toString();
	}

	/**
	 * 获取Excel数据，如果列为外键值，则找到其表的主键ID
	 * 
	 * @param orign
	 *            单元格内容
	 * @param rowData
	 *            行数据
	 * @param ec
	 * @param colNo
	 *            列号
	 * @param rowNo
	 *            行号
	 * @return
	 * @throws ServiceException
	 */
	private String getDataFromDB(String orign, String[] rowData, ExcelConfig ec, int colNo, int rowNo) throws ServiceException {
		if (StringUtils.isBlank(orign)) {
			return null;
		}
		if (StringUtils.isBlank(ec.getForeignTableName())) {
			throw new ServiceException("Excel导入配置中字段[" + ec.getColumnName() + "]对应的关联表名称 为空");
		}
		if (StringUtils.isBlank(ec.getFkColumnName())) {
			throw new ServiceException("Excel导入配置中字段[" + ec.getColumnName() + "]对应的关联字段名称为空");
		}
		StringBuilder sql = new StringBuilder("select id from ");
		sql.append(ec.getForeignTableName() + " where " + ec.getFkColumnName() + " = ? ");
		String[] fk = { orign };
		// 下面这段是为了固定外键配置值时
		if (StringUtils.isNotBlank(ec.getOtherColumns()) && StringUtils.isNotBlank(ec.getOtherValues())) {
			String[] columns = ec.getOtherColumns().split("\\|");
			String[] values = ec.getOtherValues().split("\\|");
			if (columns.length != values.length) {
				throw new ServiceException("Excel导入配置中字段[" + ec.getColumnName() + "]对应的其他关联字段集与值个数不匹配");
			}
			for (String c : columns) {
				sql.append(" and " + c + "=? ");
			}
			fk = ArrayUtils.addAll(fk, values);
		}

		// 下面这段是为了辅助外键Excel获取值
		if (StringUtils.isNotBlank(ec.getForeignTableAssistColumnName())) {
			StringBuilder sqlFK = new StringBuilder("select id from ");
			if (StringUtils.isBlank(ec.getAssistForeignTableName())) {
				throw new ServiceException("Excel导入配置中字段[" + ec.getColumnName() + "]存在关联表的辅助字段名称，但是没有配置辅助关联表名称");
			}
			sqlFK.append(ec.getAssistForeignTableName() + " where ");
			if (StringUtils.isBlank(ec.getAssistFkColumnName())) {
				throw new ServiceException("Excel导入配置中字段[" + ec.getColumnName() + "]存在关联表的辅助字段名称，但是没有配置辅助关联字段名称");
			}
			sqlFK.append(ec.getAssistFkColumnName() + " = ? ");
			if (rowData.length < ec.getAssistExcelColumnNo()) {
				throw new ServiceException("Excel导入配置中字段[" + ec.getColumnName() + "]辅助值Excel列数为" + ec.getAssistExcelColumnNo()
						+ "，但是Excel总列数才" + rowData.length);
			}
			List<String> resultFK = jdbcTemp.queryForList(sqlFK.toString(), new String[] { rowData[ec.getAssistExcelColumnNo() - 1] },
					String.class);
			if (resultFK == null || resultFK.isEmpty() || StringUtils.isBlank(resultFK.get(0))) {
				throw new ServiceException("第" + rowNo + "行第" + colNo + "列中的[" + orign + "]对应的外键辅助关联值"
						+ rowData[ec.getAssistExcelColumnNo() - 1] + "在表" + ec.getAssistForeignTableName() + "中没找到");
			}
			fk = ArrayUtils.addAll(fk, resultFK.get(0));
			sql.append(" and " + ec.getForeignTableAssistColumnName() + "=? ");
		}
		List<String> result = jdbcTemp.queryForList(sql.toString(), fk, String.class);
		if (result == null || result.isEmpty() || StringUtils.isBlank(result.get(0))) {
			throw new ServiceException("第" + rowNo + "行第" + colNo + "列中的[" + orign + "]不存在表" + ec.getForeignTableName() + "的"
					+ ec.getFkColumnName() + "列中");
		}
		return result.get(0);
	}

	private String getDataFromMap(String orign, ExcelConfig ec, int colNo, int rowNo) {
		if (StringUtils.isBlank(orign)) {
			return "";
		}
		String result = (String) ec.getColumnConvertMap().get(orign);
		if (result == null) {
			throw new ServiceException("第" + rowNo + "行第" + colNo + "列中的[" + orign + "]不存在Map配置" + ec.getConvertMap() + "中");
		}
		return result;
	}
}
